using MyBlogServer.Data;
using MyBlogServer.Models;
using Dapper;
using System.Data;
using Microsoft.Data.Sqlite;

namespace MyBlogServer.Services
{
    public class PostService
    {
        private readonly BlogContext _context;

        public PostService(BlogContext context)
        {
            _context = context;
        }

        public IEnumerable<Post> GetAllPosts(bool includeUnpublished = false)
        {
            using var connection = _context.CreateConnection() as SqliteConnection;
            connection!.Open();
            var sql = "SELECT * FROM Posts";
            
            if (!includeUnpublished)
            {
                sql += " WHERE Published = 1";
            }
            
            sql += " ORDER BY CreatedAt DESC";
            
            var posts = connection.Query<Post>(sql);
            return posts;
        }

        public async Task<IEnumerable<Post>> GetAllPostsAsync(bool includeUnpublished = false)
        {
            using var connection = _context.CreateConnection() as SqliteConnection;
            connection!.Open();
            var sql = "SELECT * FROM Posts";
            
            if (!includeUnpublished)
            {
                sql += " WHERE Published = 1";
            }
            
            sql += " ORDER BY CreatedAt DESC";
            
            var posts = await connection.QueryAsync<Post>(sql);
            return posts;
        }

        public Post? GetPostById(int id)
        {
            using var connection = _context.CreateConnection() as SqliteConnection;
            connection!.Open();
            var sql = "SELECT * FROM Posts WHERE Id = @Id";
            return connection.QueryFirstOrDefault<Post>(sql, new { Id = id });
        }

        public async Task<Post?> GetPostByIdAsync(int id)
        {
            using var connection = _context.CreateConnection() as SqliteConnection;
            connection!.Open();
            var sql = "SELECT * FROM Posts WHERE Id = @Id";
            return await connection.QueryFirstOrDefaultAsync<Post>(sql, new { Id = id });
        }

        public Post? GetPostBySlug(string slug)
        {
            using var connection = _context.CreateConnection() as SqliteConnection;
            connection!.Open();
            var sql = "SELECT * FROM Posts WHERE Slug = @Slug AND Published = 1";
            return connection.QueryFirstOrDefault<Post>(sql, new { Slug = slug });
        }

        public async Task<Post?> GetPostBySlugAsync(string slug)
        {
            using var connection = _context.CreateConnection() as SqliteConnection;
            connection!.Open();
            var sql = "SELECT * FROM Posts WHERE Slug = @Slug AND Published = 1";
            return await connection.QueryFirstOrDefaultAsync<Post>(sql, new { Slug = slug });
        }

        // 根据标签获取文章
        public IEnumerable<Post> GetPostsByTag(string tag, bool includeUnpublished = false)
        {
            using var connection = _context.CreateConnection() as SqliteConnection;
            connection!.Open();
            var sql = "SELECT * FROM Posts WHERE Tags LIKE @Tag";
            
            if (!includeUnpublished)
            {
                sql += " AND Published = 1";
            }
            
            sql += " ORDER BY CreatedAt DESC";
            
            var posts = connection.Query<Post>(sql, new { Tag = $"%{tag}%" });
            return posts;
        }

        // 根据标签获取文章 (异步版本)
        public async Task<IEnumerable<Post>> GetPostsByTagAsync(string tag, bool includeUnpublished = false)
        {
            using var connection = _context.CreateConnection() as SqliteConnection;
            connection!.Open();
            var sql = "SELECT * FROM Posts WHERE Tags LIKE @Tag";
            
            if (!includeUnpublished)
            {
                sql += " AND Published = 1";
            }
            
            sql += " ORDER BY CreatedAt DESC";
            
            var posts = await connection.QueryAsync<Post>(sql, new { Tag = $"%{tag}%" });
            return posts;
        }

        // 获取所有标签
        public IEnumerable<string> GetAllTags()
        {
            using var connection = _context.CreateConnection() as SqliteConnection;
            connection!.Open();
            var sql = "SELECT Tags FROM Posts WHERE Published = 1 AND Tags IS NOT NULL";
            var tagLists = connection.Query<string>(sql);
            
            var tags = new HashSet<string>();
            foreach (var tagList in tagLists)
            {
                var splitTags = tagList.Split(',', StringSplitOptions.RemoveEmptyEntries)
                    .Select(t => t.Trim())
                    .Where(t => !string.IsNullOrEmpty(t));
                
                tags.UnionWith(splitTags);
            }
            
            return tags.OrderBy(t => t);
        }

        public int CreatePost(Post post)
        {
            using var connection = _context.CreateConnection() as SqliteConnection;
            connection!.Open();
            var sql = @"INSERT INTO Posts (Title, Content, Summary, Published, Slug, CreatedAt, UpdatedAt, Tags, ViewCount)
                        VALUES (@Title, @Content, @Summary, @Published, @Slug, @CreatedAt, @UpdatedAt, @Tags, @ViewCount);
                        SELECT last_insert_rowid();";
            
            post.CreatedAt = DateTime.UtcNow;
            post.UpdatedAt = DateTime.UtcNow;
            
            var id = connection.QuerySingle<int>(sql, post);
            return id;
        }

        public async Task<int> CreatePostAsync(Post post)
        {
            using var connection = _context.CreateConnection() as SqliteConnection;
            connection!.Open();
            var sql = @"INSERT INTO Posts (Title, Content, Summary, Published, Slug, CreatedAt, UpdatedAt, Tags, ViewCount)
                        VALUES (@Title, @Content, @Summary, @Published, @Slug, @CreatedAt, @UpdatedAt, @Tags, @ViewCount);
                        SELECT last_insert_rowid();";
            
            post.CreatedAt = DateTime.UtcNow;
            post.UpdatedAt = DateTime.UtcNow;
            
            var id = await connection.QuerySingleAsync<int>(sql, post);
            return id;
        }

        public void UpdatePost(Post post)
        {
            using var connection = _context.CreateConnection() as SqliteConnection;
            connection!.Open();
            var sql = @"UPDATE Posts 
                        SET Title = @Title, Content = @Content, Summary = @Summary, 
                            Published = @Published, Slug = @Slug, UpdatedAt = @UpdatedAt, Tags = @Tags, ViewCount = @ViewCount
                        WHERE Id = @Id";
            
            post.UpdatedAt = DateTime.UtcNow;
            connection.Execute(sql, post);
        }

        public async Task UpdatePostAsync(Post post)
        {
            using var connection = _context.CreateConnection() as SqliteConnection;
            connection!.Open();
            var sql = @"UPDATE Posts 
                        SET Title = @Title, Content = @Content, Summary = @Summary, 
                            Published = @Published, Slug = @Slug, UpdatedAt = @UpdatedAt, Tags = @Tags, ViewCount = @ViewCount
                        WHERE Id = @Id";
            
            post.UpdatedAt = DateTime.UtcNow;
            await connection.ExecuteAsync(sql, post);
        }

        public void DeletePost(int id)
        {
            using var connection = _context.CreateConnection() as SqliteConnection;
            connection!.Open();
            var sql = "DELETE FROM Posts WHERE Id = @Id";
            connection.Execute(sql, new { Id = id });
        }

        public async Task DeletePostAsync(int id)
        {
            using var connection = _context.CreateConnection() as SqliteConnection;
            connection!.Open();
            var sql = "DELETE FROM Posts WHERE Id = @Id";
            await connection.ExecuteAsync(sql, new { Id = id });
        }
    }
}